package com.warrantyhotel.android.db;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.provider.BaseColumns;
import android.util.Log;

import com.warrantyhotel.android.db.WarrantyHotelDBSchema.CountryTable;
import com.warrantyhotel.android.db.WarrantyHotelDBSchema.CurrencyTable;
import com.warrantyhotel.android.db.WarrantyHotelDBSchema.FindStoresTable;
import com.warrantyhotel.android.db.WarrantyHotelDBSchema.GetFreeTable;
import com.warrantyhotel.android.db.WarrantyHotelDBSchema.Inbox_Account_Upload_Transfers_Table;
import com.warrantyhotel.android.db.WarrantyHotelDBSchema.Inbox_Free_Table;
import com.warrantyhotel.android.db.WarrantyHotelDBSchema.Inbox_Information_Table;
import com.warrantyhotel.android.db.WarrantyHotelDBSchema.Inbox_RSS_Table;
import com.warrantyhotel.android.db.WarrantyHotelDBSchema.Inbox_Transactions_Table;
import com.warrantyhotel.android.db.WarrantyHotelDBSchema.Inbox_WarrantyTransfer_Table;
import com.warrantyhotel.android.db.WarrantyHotelDBSchema.Inbox_Warranty_Transfer_Inner_Table;
import com.warrantyhotel.android.db.WarrantyHotelDBSchema.LanguageTable;
import com.warrantyhotel.android.db.WarrantyHotelDBSchema.News_RSS;
import com.warrantyhotel.android.db.WarrantyHotelDBSchema.RetailStoresTable;
import com.warrantyhotel.android.db.WarrantyHotelDBSchema.StateTable;
import com.warrantyhotel.android.db.WarrantyHotelDBSchema.WarrantiesTable;

/**
 * This class describes database and contains SQL-queries of tables creation.
 */

public class WarrantyHotelDatabase extends SQLiteOpenHelper {

	private static final String TAG = WarrantyHotelDatabase.class.getSimpleName();

	private static final String DATABASE_NAME = "waranntyHotel.android.db";

	private static final int DATABASE_VERSION = 66;

	public WarrantyHotelDatabase(final Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	@Override
	public void onCreate(final SQLiteDatabase db) {
		StringBuilder sql = new StringBuilder(1024);
		// Country Table creation
		sql.append("CREATE TABLE ").append(CountryTable.TABLE_NAME).append(" (").append(BaseColumns._ID).append(" INTEGER PRIMARY KEY AUTOINCREMENT, ")
				.append(CountryTable.COUNTRY_ID).append(" INTEGER,").append(CountryTable.NAME).append(" TEXT,").append(CountryTable.NAME_HACK).append(" TEXT,")
				.append(CountryTable.PHONE_PREFIX).append(" TEXT,").append(CountryTable.USEADDRESSSTATE).append(" INTEGER,").append("UNIQUE (")
				.append(CountryTable.COUNTRY_ID).append(") ON CONFLICT REPLACE)");
		db.execSQL(sql.toString());

		sql.setLength(0);
		// State Table Creation
		sql.append("CREATE TABLE ").append(StateTable.TABLE_NAME).append(" (").append(BaseColumns._ID).append(" INTEGER PRIMARY KEY AUTOINCREMENT, ")
				.append(StateTable.STATE_ID).append(" INTEGER,").append(StateTable.COUNTRY_ID).append(" TEXT,").append(StateTable.STATE_NAME).append(" TEXT,")
				.append(StateTable.STATE_NAME_HACK).append(" TEXT,").append(StateTable.STATESHORTNAME).append(" TEXT,").append("UNIQUE (")
				.append(StateTable.STATE_ID).append(") ON CONFLICT REPLACE").append(" FOREIGN KEY( ").append(StateTable.COUNTRY_ID).append(") REFERENCES ")
				.append(CountryTable.TABLE_NAME).append(" ( ").append(CountryTable.COUNTRY_ID).append(") ON DELETE CASCADE) ");
		db.execSQL(sql.toString());
		sql.setLength(0);
		// Language Table Creation
		sql.append("CREATE TABLE ").append(LanguageTable.TABLE_NAME).append(" (").append(BaseColumns._ID).append(" INTEGER PRIMARY KEY AUTOINCREMENT, ")
				.append(LanguageTable.LANGUAGE_ID).append(" INTEGER,").append(LanguageTable.ENGLISH_NAME).append(" TEXT,").append(LanguageTable.NATIVE_NAME)
				.append(" TEXT,").append(LanguageTable.CULTURE).append(" TEXT,").append("UNIQUE (").append(LanguageTable.LANGUAGE_ID)
				.append(") ON CONFLICT REPLACE )");
		db.execSQL(sql.toString());
		sql.setLength(0);

		// ContentValues values = new ContentValues();
		// values.put(LanguageTable._ID, "1");

		// TODO

		// values.put(LanguageTable.ENGLISH_NAME, "Select language");
		// long count = db.insert(LanguageTable.TABLE_NAME, null, values);
		// Log.e("count 11", "" + count);

		// Currency Table Creation
		sql.append("CREATE TABLE ").append(CurrencyTable.TABLE_NAME).append(" (").append(BaseColumns._ID).append(" INTEGER PRIMARY KEY AUTOINCREMENT, ")
				.append(CurrencyTable.CURRENCY_ID).append(" TEXT,").append(CurrencyTable.LABEL).append(" TEXT,").append(CurrencyTable.PREFIX).append(" TEXT,")
				.append("UNIQUE (").append(CurrencyTable.CURRENCY_ID).append(") ON CONFLICT REPLACE )");
		db.execSQL(sql.toString());
		sql.setLength(0);
		// Warranty Table Creation
		sql.append("CREATE TABLE ").append(WarrantiesTable.TABLE_NAME).append(" (").append(BaseColumns._ID).append(" INTEGER PRIMARY KEY AUTOINCREMENT, ")
				.append(WarrantiesTable.WARRANTY_ID)
				.append(" INTEGER,")
				.append(WarrantiesTable.WARRANTY_TYPE)
				.append(" INTEGER,")
				.append(WarrantiesTable.ACCOUNT_ID)
				.append(" TEXT,")
				.append(WarrantiesTable.PRODUCT_ID)
				.append(" TEXT,")
				.append(WarrantiesTable.PRODUCT_NAME)
				.append(" TEXT,")
				.append(WarrantiesTable.RETAIL_STORE_ID)
				.append(" TEXT,")
				.append(WarrantiesTable.RETAIL_STORE_NAME)
				.append(" TEXT,")
				.append(WarrantiesTable.RETAIL_NAME)
				.append(" TEXT,")
				// .append(WarrantiesTable.USER_LOGIN).append(" TEXT,")
				.append(WarrantiesTable.PRODUCT_PRICE).append(" TEXT,").append(WarrantiesTable.LIST_LABEL).append(" TEXT,").append(WarrantiesTable.IMAGE_URL)
				.append(" TEXT,").append(WarrantiesTable.AREA_LIMIT_TEXT).append(" TEXT,").append(WarrantiesTable.SERVICE_NUMBER).append(" TEXT,")
				.append(WarrantiesTable.SERVICE_EMAIL).append(" TEXT,").append(WarrantiesTable.DESCRIPTION).append(" TEXT,")
				.append(WarrantiesTable.ADDRESS_CITY).append(" TEXT,").append(WarrantiesTable.ADDRESS_COUNTRY_ID).append(" TEXT,")
				.append(WarrantiesTable.ADDRESS_POSTAL_CODE).append(" TEXT,").append(WarrantiesTable.ADDRESS_STATE_ID).append(" TEXT,")
				.append(WarrantiesTable.ADDRESS_STREET).append(" TEXT,").append(WarrantiesTable.FILE_NAME).append(" TEXT,")
				.append(WarrantiesTable.MANUFACTURER_NAME).append(" TEXT,").append(WarrantiesTable.END_DATE).append(" TEXT,")
				.append(WarrantiesTable.PRODUCT_NAME_TO_UPLOAD).append(" TEXT,").append(WarrantiesTable.STORE_NAME).append(" NUMERIC,")
				.append(WarrantiesTable.PURCHASE_DATE).append(" TEXT,").append(WarrantiesTable.CREATED_DATE).append(" TEXT,")
				.append(WarrantiesTable.EXPIRATION_DATE).append(" TEXT,").append(WarrantiesTable.FILE_URL).append(" TEXT,")
				.append(WarrantiesTable.CONTENT_TYPE_WARRANTY).append(" TEXT,").append(WarrantiesTable.THUMBNAIL_URL).append(" TEXT,")
				.append(WarrantiesTable.LATITUDE).append(" TEXT,").append(WarrantiesTable.LONGTITUDE).append(" TEXT,").append(WarrantiesTable.COMMENTS)
				.append(" TEXT,").append("UNIQUE (").append(WarrantiesTable.WARRANTY_ID).append(",").append(WarrantiesTable.WARRANTY_TYPE)
				.append(") ON CONFLICT REPLACE )");
		db.execSQL(sql.toString());
		sql.setLength(0);
		// News_RSS
		sql.append("CREATE TABLE ").append(News_RSS.TABLE_NAME).append(" (").append(BaseColumns._ID).append(" INTEGER PRIMARY KEY AUTOINCREMENT, ")
				.append(News_RSS.GUID).append(" TEXT,").append(News_RSS.LINK).append(" TEXT,").append(News_RSS.LINK_THUMBNAIL).append(" TEXT,")
				.append(News_RSS.LINK_BANNER).append(" TEXT,").append(News_RSS.TITLE).append(" TEXT,").append(News_RSS.DESCRIPTION).append(" TEXT,")
				.append(News_RSS.DATE_UPDATE).append(" TEXT,").append("UNIQUE (").append(News_RSS.GUID).append(") ON CONFLICT REPLACE )");
		db.execSQL(sql.toString());
		sql.setLength(0);
		// Find Stores
		sql.append("CREATE TABLE ").append(FindStoresTable.TABLE_NAME).append(" (").append(BaseColumns._ID).append(" INTEGER PRIMARY KEY AUTOINCREMENT, ")
				.append(FindStoresTable.RETAIL_ID).append(" TEXT,").append(FindStoresTable.RETAIL_STORE_ID).append(" TEXT,").append(FindStoresTable.NAME)
				.append(" TEXT,").append(FindStoresTable.PHONE).append(" TEXT,").append(FindStoresTable.PHONE_PREFIX).append(" TEXT,")
				.append(FindStoresTable.SERVICE_EMAIL).append(" TEXT,").append(FindStoresTable.SERVICE_NUMBER).append(" TEXT,")
				.append(FindStoresTable.LOCATION_LATITUDE).append(" NUMERIC,").append(FindStoresTable.LOCATION_LONGITUDE).append(" NUMERIC,")
				.append(FindStoresTable.DESCRIPTION).append(" TEXT,").append(FindStoresTable.INFO).append(" TEXT,").append(FindStoresTable.MAP_IMAGE_URL)
				.append(" TEXT,").append(FindStoresTable.THUMBNAIL).append(" TEXT,").append(FindStoresTable.POI).append(" INTEGER,")
				.append(FindStoresTable.BANNER).append(" TEXT,").append("UNIQUE (").append(FindStoresTable.RETAIL_STORE_ID).append(") ON CONFLICT REPLACE )");
		db.execSQL(sql.toString());
		sql.setLength(0);
		// Get Free
		sql.append("CREATE TABLE ").append(GetFreeTable.TABLE_NAME).append(" (").append(BaseColumns._ID).append(" INTEGER PRIMARY KEY AUTOINCREMENT, ")
				.append(GetFreeTable.GET_FREE_ID).append(" TEXT,").append(GetFreeTable.GET_FREE_ACTION_TYPE_ID).append(" TEXT,").append(GetFreeTable.LABEL)
				.append(" TEXT,").append(GetFreeTable.CREATED_DATE).append(" TEXT,").append(GetFreeTable.ACTION_TITLE).append(" TEXT,")
				.append(GetFreeTable.ACTION_TEXT).append(" TEXT,").append(GetFreeTable.AWARD_UPLOADS).append(" INTEGER,").append(GetFreeTable.AWARD_CREDITS)
				.append(" INTEGER,").append(GetFreeTable.THUMBNAIL_LINK).append(" TEXT,").append(GetFreeTable.BANNER_LINK).append(" TEXT,").append("UNIQUE (")
				.append(GetFreeTable.GET_FREE_ID).append(") ON CONFLICT REPLACE )");
		db.execSQL(sql.toString());
		sql.setLength(0);
		// Inbox Information
		sql.append("CREATE TABLE ").append(Inbox_Information_Table.TABLE_NAME).append(" (").append(BaseColumns._ID)
				.append(" INTEGER PRIMARY KEY AUTOINCREMENT, ").append(Inbox_Information_Table.INFO_ID).append(" TEXT,").append(Inbox_Information_Table.TEXT)
				.append(" TEXT,").append(Inbox_Information_Table.TITLE).append(" TEXT,").append(Inbox_Information_Table.EXPIRES).append(" TEXT,")
				.append(Inbox_Information_Table.ISVISIBLE).append(" INTEGER,").append(Inbox_Information_Table.NAME_TABLE).append(" TEXT,").append("UNIQUE (")
				.append(Inbox_Information_Table.INFO_ID).append(") ON CONFLICT IGNORE )");
		db.execSQL(sql.toString());
		sql.setLength(0);
		// Inbox Information
		sql.append("CREATE TABLE ").append(Inbox_Free_Table.TABLE_NAME).append(" (").append(BaseColumns._ID).append(" INTEGER PRIMARY KEY AUTOINCREMENT, ")
				.append(Inbox_Free_Table.GET_FREE_ID).append(" TEXT,").append(Inbox_Free_Table.GET_FREE_ACTION_TYPE_ID).append(" TEXT,")
				.append(Inbox_Free_Table.LABEL).append(" TEXT,").append(Inbox_Free_Table.CREATED_DATE).append(" TEXT,").append(Inbox_Free_Table.ACTION_TITLE)
				.append(" TEXT,").append(Inbox_Free_Table.ACTION_TEXT).append(" TEXT,").append(Inbox_Free_Table.AWARD_UPLOADS).append(" INTEGER,")
				.append(Inbox_Free_Table.AWARD_CREDITS).append(" INTEGER,").append(Inbox_Free_Table.THUMBNAIL_LINK).append(" TEXT,")
				.append(Inbox_Free_Table.BANNER_LINK).append(" TEXT,").append(Inbox_Free_Table.NAME_TABLE).append(" TEXT,").append(Inbox_Free_Table.END_DATE)
				.append(" INTEGER,").append(Inbox_Free_Table.ISVISIBLE).append(" TEXT,").append("UNIQUE (").append(Inbox_Free_Table.GET_FREE_ID)
				.append(") ON CONFLICT IGNORE )");
		db.execSQL(sql.toString());
		sql.setLength(0);
		// Inbox RSS
		sql.append("CREATE TABLE ").append(Inbox_RSS_Table.TABLE_NAME).append(" (").append(BaseColumns._ID).append(" INTEGER PRIMARY KEY AUTOINCREMENT, ")
				.append(Inbox_RSS_Table.GUID).append(" TEXT,").append(Inbox_RSS_Table.LINK).append(" TEXT,").append(Inbox_RSS_Table.LINK_THUMBNAIL)
				.append(" TEXT,").append(Inbox_RSS_Table.LINK_BANNER).append(" TEXT,").append(Inbox_RSS_Table.TITLE).append(" TEXT,")
				.append(Inbox_RSS_Table.DESCRIPTION).append(" TEXT,").append(Inbox_RSS_Table.CONTENT).append(" TEXT,").append(Inbox_RSS_Table.DATE_UPDATE)
				.append(" TEXT,").append(Inbox_RSS_Table.NAME_TABLE).append(" TEXT,").append("UNIQUE (").append(Inbox_RSS_Table.GUID)
				.append(") ON CONFLICT REPLACE )");
		db.execSQL(sql.toString());
		sql.setLength(0);
		// Inbox Transaction
		sql.append("CREATE TABLE ").append(Inbox_Transactions_Table.TABLE_NAME).append(" (").append(BaseColumns._ID)
				.append(" INTEGER PRIMARY KEY AUTOINCREMENT, ").append(Inbox_Transactions_Table.GUID).append(" TEXT,").append(Inbox_Transactions_Table.TITLE)
				.append(" TEXT,").append(Inbox_Transactions_Table.TEASER).append(" TEXT,").append(Inbox_Transactions_Table.NAME_TABLE).append(" TEXT,")
				.append("UNIQUE (").append(Inbox_Transactions_Table.GUID).append(") ON CONFLICT REPLACE )");
		db.execSQL(sql.toString());
		sql.setLength(0);
		// Inbox Warranty Transaction
		sql.append("CREATE TABLE ").append(Inbox_Warranty_Transfer_Inner_Table.TABLE_NAME).append(" (").append(BaseColumns._ID)
				.append(" INTEGER PRIMARY KEY AUTOINCREMENT, ").append(Inbox_Warranty_Transfer_Inner_Table.WARRANTY_ID).append(" TEXT,")
				.append(Inbox_Warranty_Transfer_Inner_Table.WARRANTY_TRANSACTION_ID).append(" TEXT,").append(Inbox_Warranty_Transfer_Inner_Table.ITEM_NAME)
				.append(" TEXT,").append(Inbox_Warranty_Transfer_Inner_Table.STORE_NAME).append(" TEXT,").append("UNIQUE (")
				.append(Inbox_Warranty_Transfer_Inner_Table.WARRANTY_ID).append(") ON CONFLICT REPLACE ").append(" FOREIGN KEY( ")
				.append(Inbox_Warranty_Transfer_Inner_Table.WARRANTY_TRANSACTION_ID).append(") REFERENCES ").append(Inbox_Transactions_Table.TABLE_NAME)
				.append(" ( ").append(Inbox_Transactions_Table.GUID).append(") ON DELETE CASCADE) ");
		// TODO
		db.execSQL(sql.toString());
		sql.setLength(0);
		// Inbox Inbox_WarrantyTransfer_Table
		sql.append("CREATE TABLE ").append(Inbox_WarrantyTransfer_Table.TABLE_NAME).append(" (").append(BaseColumns._ID)
				.append(" INTEGER PRIMARY KEY AUTOINCREMENT, ").append(Inbox_WarrantyTransfer_Table.GUID).append(" TEXT,")
				.append(Inbox_WarrantyTransfer_Table.LAST_CHANGED_DATE).append(" TEXT,").append(Inbox_WarrantyTransfer_Table.NAME_TABLE).append(" TEXT,")
				.append(Inbox_WarrantyTransfer_Table.TEASER).append(" TEXT,").append(Inbox_WarrantyTransfer_Table.ITEM_NAME).append(" TEXT,")
				.append(Inbox_WarrantyTransfer_Table.STORE_NAME).append(" TEXT,").append(Inbox_WarrantyTransfer_Table.TITLE).append(" TEXT,")
				.append("UNIQUE (").append(Inbox_WarrantyTransfer_Table.GUID).append(") ON CONFLICT REPLACE )");
		db.execSQL(sql.toString());
		sql.setLength(0);
		// Inbox Account_Upload_Transfers_Table
		sql.append("CREATE TABLE ").append(Inbox_Account_Upload_Transfers_Table.TABLE_NAME).append(" (").append(BaseColumns._ID)
				.append(" INTEGER PRIMARY KEY AUTOINCREMENT, ").append(Inbox_Account_Upload_Transfers_Table.GUID).append(" TEXT,")
				.append(Inbox_Account_Upload_Transfers_Table.EXPIRATION_DATE).append(" TEXT,").append(Inbox_Account_Upload_Transfers_Table.FILE_URL)
				.append(" TEXT,").append(Inbox_Account_Upload_Transfers_Table.PRODUCTNAME).append(" TEXT,")
				.append(Inbox_Account_Upload_Transfers_Table.PURCHASE_DATE).append(" TEXT,").append(Inbox_Account_Upload_Transfers_Table.STORE_NAME)
				.append(" TEXT,").append(Inbox_Account_Upload_Transfers_Table.NAME_TABLE).append(" TEXT,").append(Inbox_Account_Upload_Transfers_Table.TEASER)
				.append(" TEXT,").append(Inbox_Account_Upload_Transfers_Table.TITLE).append(" TEXT,").append("UNIQUE (")
				.append(Inbox_Account_Upload_Transfers_Table.GUID).append(") ON CONFLICT REPLACE )");
		db.execSQL(sql.toString());
		sql.setLength(0);

		// Retail Stores
		sql.append("CREATE TABLE ").append(RetailStoresTable.TABLE_NAME).append(" (").append(BaseColumns._ID).append(" INTEGER PRIMARY KEY AUTOINCREMENT, ")
				.append(RetailStoresTable.RETAIL_STORE_ID).append(" TEXT,").append(RetailStoresTable.RETAIL_ID).append(" TEXT,").append(RetailStoresTable.NAME)
				.append(" TEXT,").append(RetailStoresTable.ADDRESS_STREET).append(" TEXT,").append(RetailStoresTable.ADDRESS_POSTAL_CODE).append(" TEXT,")
				.append(RetailStoresTable.ADDRES_SCITY).append(" TEXT,").append(RetailStoresTable.PHONE_PREFIX).append(" TEXT,")
				.append(RetailStoresTable.PHONE).append(" TEXT,").append(RetailStoresTable.SERVICE_NUMBER).append(" TEXT,")
				.append(RetailStoresTable.SERVICE_EMAIL).append(" TEXT,").append(RetailStoresTable.LONGTITUDE).append(" TEXT,")
				.append(RetailStoresTable.LATITUDE).append(" TEXT,").append(RetailStoresTable.ADDRESS_STATE_ID).append(" TEXT,")
				.append(RetailStoresTable.ADDRESS_COUNTRY_ID).append(" TEXT,").append(RetailStoresTable.BANNER).append(" TEXT,")
				.append(RetailStoresTable.DESCRIPTION).append(" TEXT,").append(RetailStoresTable.INFO).append(" TEXT,").append("UNIQUE (")
				.append(RetailStoresTable.RETAIL_STORE_ID).append(") ON CONFLICT REPLACE )");
		db.execSQL(sql.toString());
		sql.setLength(0);
	}

	@Override
	public void onUpgrade(final SQLiteDatabase db, final int oldVersion, final int newVersion) {
		Log.d(TAG, "Upgrading database from v." + oldVersion + " to v." + newVersion);

		// drop all tables
		final String tables[] = { CountryTable.TABLE_NAME, StateTable.TABLE_NAME, LanguageTable.TABLE_NAME, CurrencyTable.TABLE_NAME,
				WarrantiesTable.TABLE_NAME, News_RSS.TABLE_NAME, FindStoresTable.TABLE_NAME, GetFreeTable.TABLE_NAME, Inbox_Information_Table.TABLE_NAME,
				Inbox_Free_Table.TABLE_NAME, Inbox_RSS_Table.TABLE_NAME, Inbox_Transactions_Table.TABLE_NAME, RetailStoresTable.TABLE_NAME,
				Inbox_WarrantyTransfer_Table.TABLE_NAME, Inbox_Account_Upload_Transfers_Table.TABLE_NAME, Inbox_Warranty_Transfer_Inner_Table.TABLE_NAME };
		for (final String table : tables)
			db.execSQL("DROP TABLE IF EXISTS " + table);

		onCreate(db);
	}

}
